<#----------------------------------------------------------------------------------
        NAME: Upsert-Database.ps1
       EMAIL: saleyoun@hotmail.com
 DESCRIPTION: This script inserts a record in the DatabaseFile table in the iSQLPS
              system. If the record already exists, then this script updates the
              existing record.
------------------------------------------------------------------------------------
  DISCLAIMER: The AUTHOR ASSUMES NO RESPONSIBILITY FOR ANYTHING, including the 
  destruction of personal property, creating singularities, making deep fried chicken,
  causing your toilet to explode, making your animals spin around like mad, causing 
  hair loss, killing your buzz or ANYTHING else that can be thought up.
------------------------------------------------------------------------------------#>

[String] $strUpsertSql=""
[String] $strQuerySql=""

$strQuerySql = @"
SELECT CASE WHEN S.InstanceName != 'MSSQLSERVER' THEN H.hostName +'\'+ S.InstanceName
            ELSE H.HostName END [SQLNetworkName],
       H.HostName,            
       S.ServerSQLID, 
       S.TCPPort
  FROM dbo.ServerSQL S
  JOIN dbo.ServerOS H on H.ServerOSID = S.ServerOSID AND
       S.Active = 1 AND
       H.Active = 1
"@

try {
    $sqlServers = Invoke-Sqlcmd -Query $strQuerySql -ServerInstance $iSQLPSServer -Database $iSQLPSDatabase
}
catch {
    Write-Host("An error occurred while retrieving the list of servers from the inventory database.")
}

Foreach ($sqlServer in $sqlServers) {
    $strUpsertSql = ""

	$sqlNetworkName = $sqlServer.SQLNetworkName
    $sqlHostName = $sqlServer.HostName
	$sqlServerID = $sqlServer.ServerSQLID
	$sqlTcpPort = $sqlServer.TCPPort

    try {
        if (IsHostReachable $sqlHostName) {
            $sqlDatabases = get-sqldatabase $sqlNetworkName 
        }
    }
    catch {
        Write-Host("An error occurred while retrieving the list of database from $sqlNetworkName")
    }

	if ($sqlDatabases) {
		Foreach ($sqlDatabase in $sqlDatabases) {
            $fileGroups = $sqlDatabase.FileGroups
            $logFiles = $sqlDatabase.LogFiles
			
			Foreach ($fileGroup in $FileGroups) {
				$files = $fileGroup.Files
				Foreach ($file in $Files) {
					$strUpsertSql += "EXEC dbo.UpsertDatabaseFile " + $sqlServerID + ", '" + $sqlDatabase.name + "', '" + $file.Name + "', '" + $file.FileName + "', " + $file.Size + ", " + $file.UsedSpace + ", " + $file.Growth + ", '" + $file.GrowthType + "', " + $file.MaxSize + ";`n"
				}
			}

            Foreach ($logFile in $logFiles) {
                $strUpsertSql += "EXEC dbo.UpsertDatabaseFile " + $sqlServerID + ", '" + $sqlDatabase.name + "', '" + $logFile.Name + "', '" + $logFile.FileName + "', " + $logFile.Size + ", " + $logFile.UsedSpace + ", " + $logFile.Growth + ", '" + $logFile.GrowthType + "', " + $logFile.MaxSize + ";`n"
            }
        }
    }
    else {
    $strUpsertSql = ""
	}

	if ($strUpsertSql.Length -gt 0) {
        try {
            Invoke-Sqlcmd -Query $strUpsertSql -ServerInstance $iSQLPSServer -Database $iSQLPSDatabase
            $strUpsertSql
        }
        catch {
            Write-Host("An error occurred while inserting database information in the inventory database.")
        }
    }
}
